﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data.OleDb;
using System.Xml;
namespace DAO
{
    public class SoDuDAO
    {
        /// <summary>
        /// 
        /// </summary>
        /// <param name="date"> Kiem tra Thang truyen vao duoc ghi nhan(theo doi) trong bang SoDu hay chua</param>
        /// <returns> tra ve 1 neu da duoc theo doi, tra ve 0 neu chua duoc theo doi</returns>
        public int ktTonTai_Thang(DateTime date)
        {
            OleDbConnection cnn = DAO.DataProvider.ConnectDB();
            try
            {

                string sql = "SELECT SoDu.Ngay FROM SoDu WHERE Month(SoDu.Ngay)=" + date.Month.ToString();
                OleDbCommand cmd = new OleDbCommand();
                cmd.Connection = cnn;
                cmd.CommandText = sql;
                OleDbDataReader rd = cmd.ExecuteReader();
                if (rd.HasRows == true)
                    return 1;
                return 0;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                cnn.Close();
            }
        }
        /// <summary>
        /// Neu <Thang> chua duoc ghi nhan thi them vao bang SODU
        /// </summary>
        /// <param name="date"></param>
        public void ThemSoDuThangMoi(DateTime date)
        {
            date = date.AddMonths(1);
            if (ktTonTai_Thang(date) == 1)
            {
                return;
                //throw new Exception("Da Co");
            }
            SanPhamDAO sp = new SanPhamDAO();
            List<SanPhamDTO> lst = sp.LayDSSanPham();
            OleDbConnection conn = DAO.DataProvider.ConnectDB();
            OleDbParameter pa;
            OleDbCommand cmd = new OleDbCommand();
            try
            {
                cmd.Connection = conn;
                pa = new OleDbParameter("@Ngay", OleDbType.VarChar);
                cmd.Parameters.Add(pa);
                pa = new OleDbParameter("@MaSP", OleDbType.Integer);
                cmd.Parameters.Add(pa);
                pa = new OleDbParameter("@SoLuong", OleDbType.Integer);
                cmd.Parameters.Add(pa);
                for (int i = 0; i < lst.Count; i++)
                {
                    string sql = "insert into SoDu(Ngay,MaSP,SoDu) values(@Ngay,@MaSP,@SoLuong)";
                    cmd.CommandText = sql;
                    cmd.Parameters[0].Value = date.ToShortDateString();
                    cmd.Parameters[1].Value = lst[i].MaSP;
                    cmd.Parameters[2].Value = lst[i].SLTon;
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
                cmd.Dispose();
            }

        }
        /// <summary>
        /// cap nhat so du cho tung san pham trong bang so du
        /// </summary>
        /// <param name="sodu"> sodu.SoDu luu so san pham vua duoc ban</param>
        public void CapNhatSoDu_TungSanPham(SoDuDTO sodu)
        {
            OleDbConnection conn = DataProvider.ConnectDB();
            string sql = "UPDATE SoDU set SoDu=SoDu-@sl where MaSP=" + sodu.MaSP.ToString() + " and Month(SoDu.Ngay)=" + sodu.Ngay.Month.ToString() + " and Year(SoDu.Ngay)=" + sodu.Ngay.Year.ToString();
            OleDbParameter pa = new OleDbParameter("@sl", OleDbType.Integer);
            pa.Value = sodu.SoDu;
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            cmd.Parameters.Add(pa);
            cmd.ExecuteNonQuery();
            conn.Close();
            cmd.Dispose();
        }

    }
}
